package com.example.demo2.mapper;

import com.example.demo2.dto.SampleDTO;
import com.example.demo2.entity.*;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface SampleMapper {
    @Select("select * from sample where spId=#{spId}")
    public Sample findSampleBySpId(Integer spId);

    @Select("select * from sample where spId = #{spId}")
    public Sample FindSampleByid(@Param("spId") int spId);

    @Select("select * from sample where staId = #{staId} ")
    public List<Sample> FindAllSample(@Param("staId") int staId);

    @Insert("insert into sample_orders (uID,spId,spoNeed,spoStatus,spoGg) values (#{uID},#{spId},#{spNeed},#{spStatus},#{spoGg})")
    public int AddSampleorders(@Param("uID") int uID, @Param("spId") int spId, @Param("spNeed") String spNeed, @Param("spStatus") String spStatus, @Param("spoGg") String spoGg);

    @Select("SELECT * FROM sample WHERE spId = #{spId}")
    Sample getSampleById(int spId);

    @Select("SELECT * FROM sample")
    List<Sample> getAllSamples();

    @Insert("INSERT INTO sample (spName, staId, spFactory, spGg) " +
            "VALUES (#{spName}, #{staId}, #{spFactory}, #{spGg})")
    @Options(useGeneratedKeys = true, keyProperty = "spId")
    void insertSample(Sample sample);

    @Update("UPDATE sample SET spName = #{spName}, staId = #{staId}, spFactory = #{spFactory}, spGg = #{spGg} " +
            "WHERE spId = #{spId}")
    void updateSample(Sample sample);

    @Delete("DELETE FROM sample WHERE spId = #{spId}")
    void deleteSample(int spId);

    // 查询所有状态为“未发放”的样品
    @Select("SELECT DISTINCT so.spoId, s.staName,  u.uRealName, sp.spName, sp.spFactory, sp.spGg, so.spoNeed, so.spoStatus " +
            "FROM sample_orders so " +
            "JOIN sample sp ON so.spId = sp.spId " +
            "JOIN standard s ON sp.staId = s.staId " +
            "JOIN users u ON u.uID = so.uID " +
            "WHERE so.spoStatus = '未发放'")
    List<Map<String, Object>> getPendingSamples();

    // 已发放
    @Select("SELECT DISTINCT so.spoId, s.staName, u.uRealName, sp.spName, sp.spFactory, sp.spGg, so.spoNeed, so.spoStatus " +
            "FROM sample_orders so " +
            "JOIN sample sp ON so.spId = sp.spId " +
            "JOIN standard s ON sp.staId = s.staId " +
            "JOIN users u ON u.uID = so.uID " +
            "WHERE so.spoStatus = '已发放'")
    List<Map<String, Object>> getCompletedSamples();


    // 更新样品状态为“已发放”
    @Update("UPDATE sample_orders SET spoStatus = '已发放' WHERE spoId = #{spoId}")
    void updateSampleStatusToCompleted(int spoId);

    // 删除指定样品
    @Delete("DELETE FROM sample_orders WHERE spoId = #{spoId}")
    void deleteSampleById(int spoId);

    // 批量删除样品清单
    @DeleteProvider(type = SqlProvider.class, method = "deleteSamplesByIds")
    void deleteSamplesByIds(@Param("spIds") List<Integer> spIds);

    class SqlProvider {
        public static String deleteSamplesByIds(Map<String, Object> params) {
            @SuppressWarnings("unchecked")
            List<Integer> ids = (List<Integer>) params.get("spIds");
            StringBuilder sql = new StringBuilder();
            sql.append("DELETE FROM sample_orders WHERE spoId IN (");
            for (int i = 0; i < ids.size(); i++) {
                sql.append(ids.get(i));
                if (i < ids.size() - 1) {
                    sql.append(",");
                }
            }
            sql.append(")");
            return sql.toString();
        }
    }

    // 获取指定 staId 的样本列表
    @Select("SELECT spId, spName, spFactory, spGg, staId " +
            "FROM sample " +
            "WHERE staId = #{staId}")
    List<SampleDTO> getSamples(@Param("staId") int staId);

    // 根据 spId 更新样本信息
    @Update("UPDATE sample SET " +
            "spName = #{spName}, " +
            "spFactory = #{spFactory}, " +
            "spGg = #{spGg} " +
            "WHERE spId = #{spId}")
    int updateSampleBySpId(SampleDTO sample);

    // 根据 spId 删除样本
    @Delete("DELETE FROM sample WHERE spId = #{spId}")
    int deleteSampleBySpId(@Param("spId") int spId);

    // 根据 staId 添加新样本
    @Insert("INSERT INTO sample (spName, spFactory, spGg, staId) " +
            "VALUES (#{spName}, #{spFactory}, #{spGg}, #{staId})")
    @Options(useGeneratedKeys = true, keyProperty = "spId")
    int addSample(SampleDTO sample);


}